from flask import Flask, request, render_template
import requests
import pandas as pd
import mysql.connector
from datetime import datetime
from flask import jsonify  # 用于返回 JSON 格式数据


app = Flask(__name__)

# ✅ 天气 API 地址
WEATHER_API_URL = "http://u.api.xdapi.com/api/v2/Weather/city"

# ✅ Excel 文件名
EXCEL_FILENAME = "AreaCode.xls"

# ✅ 构建 {城市区域名: AreaCode} 映射字典
city_area_code_map = {}

try:
    df = pd.read_excel(EXCEL_FILENAME)
    city_name_col = '城市区域名'
    area_code_col = 'AreaCode'
    city_area_code_map = dict(zip(df[city_name_col], df[area_code_col].astype(str)))
    print(f"[INFO] 成功加载 {len(city_area_code_map)} 个城市区域信息")
except Exception as e:
    print(f"[ERROR] 读取 Excel 文件 {EXCEL_FILENAME} 失败：{e}")

@app.route('/', methods=['GET', 'POST'])
def index():
    weather_info = None
    error = None
    search_keyword = request.form.get('search', '').strip()
    matched_cities = []  # 本轮搜索匹配到的城市列表
    selected_city = None  # 用户从匹配列表中点击选择的城市

    if request.method == 'POST':
        # 情况1：用户刚输入关键字，点击查询 → 需要返回匹配的城市列表
        search_keyword = request.form.get('search', '').strip()
        if search_keyword:
            matched_cities = [
                city for city in city_area_code_map.keys()
                if search_keyword.lower() in city.lower()  # 不区分大小写，包含匹配
            ]

            # 如果没有匹配项
            if not matched_cities:
                error = f"未找到包含「{search_keyword}」的城市区域，请尝试其它关键词"
            # 如果有匹配项，返回给前端让用户选择
            # selected_city 暂时为空，等待用户点击

        # 情况2：用户已经从匹配列表中选择了某个城市（通过表单提交）
        selected_city = request.form.get('selected_city')
        if selected_city and selected_city in city_area_code_map:
            area_code = city_area_code_map[selected_city]

            try:
                params = {'code': area_code}
                response = requests.get(WEATHER_API_URL, params=params, timeout=10)
                response.raise_for_status()
                api_result = response.json()

                if api_result.get('code') == 1 and api_result.get('msg') == '查询成功':
                    data_list = api_result.get('data', [])
                    if data_list and len(data_list) > 0:
                        weather = data_list[0]
                        weather_info = {
                            'province': weather.get('province'),
                            'city': weather.get('city'),
                            'adcode': weather.get('adcode'),
                            'weather': weather.get('weather'),
                            'temperature': weather.get('temperature'),
                            'winddirection': weather.get('winddirection'),
                            'windpower': weather.get('windpower'),
                            'humidity': weather.get('humidity'),
                            'reporttime': weather.get('reporttime')
                        }

                        # ✅ ===== 新增：保存查询记录到 MySQL =====
                        if weather_info:
                            save_search_to_mysql(
                                city_code=area_code,
                                city=weather.get('city'),
                                weather=weather.get('weather'),
                                temp=weather.get('temperature'),  # 可能是 '26' 或 '26°C'
                                wind_direction=weather.get('winddirection'),
                                wind_power=weather.get('windpower'),  # 可能是 '≤3' 或 '3级'
                                humidity=weather.get('humidity')  # 可能是 '65%' 或 '65'
                            )
                        # =========================================
                    else:
                        error = "未找到该区域码对应的天气信息"
                else:
                    err_msg = api_result.get('msg', '未知错误')
                    error = f"天气 API 错误：{err_msg}"

            except Exception as e:
                error = f"查询天气时发生错误：{e}"

    return render_template(
        'index.html',
        weather=weather_info,
        error=error,
        search_keyword=search_keyword,
        matched_cities=matched_cities,
        selected_city=selected_city
    )

def save_search_to_mysql(city_code, city, weather, temp, wind_direction, wind_power, humidity):
    try:
        # 🔌 连接到 MySQL 数据库（请根据您的实际配置修改以下参数）
        connection = mysql.connector.connect(
            host="127.0.0.1",      # MySQL 服务器地址（如 Docker / 云数据库，请改成对应 IP）
            user="root",           # MySQL 用户名
            password="31840",  # MySQL 密码
            database="pythondb"   # 数据库名（请替换为您的实际数据库名）
        )

        cursor = connection.cursor()

        # 📅 获取当前日期
        today = datetime.now().date()

        # ✅ 插入数据的 SQL（注意：query_id 是自增主键，不需要传！）
        insert_query = """
        INSERT INTO SearchHistory 
        (city_code, city, weather, temp, wind_direction, wind_power, humidity, data_DATE)
        VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
        """

        # 🧩 执行插入，传入所有字段值（除 query_id）
        cursor.execute(insert_query, (
            city_code,          # 例如：500101
            city,               # 例如：万州区
            weather,            # 例如：晴
            temp,               # 例如：26
            wind_direction,     # 例如：东北
            wind_power,         # 例如：3
            humidity,           # 例如：65
            today               # 例如：2024-06-01
        ))

        # ✅ 提交事务
        connection.commit()
        print("[MySQL] ✅ 查询记录已成功保存到数据库")

    except Exception as e:
        print("[MySQL ❌ 错误] 保存失败：", e)
    finally:
        # ✅ 关闭连接（务必！）
        if connection.is_connected():
            cursor.close()
            connection.close()

@app.route('/query_records')
def query_records():
    try:
        # ✅ 正确：使用关键字参数连接 MySQL
        connection = mysql.connector.connect(
            host="127.0.0.1",      # MySQL 服务器地址（如果是 Docker 或远程，请改 IP）
            user="root",           # MySQL 用户名
            password="31840",      # MySQL 密码，请根据您的实际配置修改
            database="pythondb"    # 数据库名，请根据您的实际配置修改
        )

        cursor = connection.cursor(dictionary=True)  # 返回字典格式结果，方便使用

        # 📅 获取今天的日期
        today = datetime.now().date()

        # ✅ 查询今天所有查询记录
        query = """
        SELECT city, weather, temp, wind_direction, wind_power, humidity, data_DATE
        FROM SearchHistory
        WHERE data_DATE = %s
        ORDER BY query_id DESC
        """
        cursor.execute(query, (today,))
        records = cursor.fetchall()  # 列表，每个元素是一个字典

        # ✅ 关闭连接
        cursor.close()
        connection.close()

        # ✅ 渲染查询记录页面，传入记录和当天日期
        return render_template('query_records.html', records=records, today=today)

    except Exception as e:
        print("[ERROR] 查询记录失败：", e)
        return f"查询记录时发生错误：{e}", 500

@app.route('/api/query_records', methods=['GET'])
def api_query_records():
    try:
        # 🔌 连接 MySQL 数据库
        connection = mysql.connector.connect(
            host="127.0.0.1",      # 请根据您的实际配置修改
            user="root",
            password="31840",
            database="pythondb"
        )
        cursor = connection.cursor(dictionary=True)

        # 📅 获取今天的日期（您也可以参数化，比如传入日期）
        today = datetime.now().date()

        # ✅ 查询当天所有查询记录
        query = """
        SELECT city, weather, temp, wind_direction, wind_power, humidity, data_DATE
        FROM SearchHistory
        WHERE data_DATE = %s
        ORDER BY query_id DESC
        """
        cursor.execute(query, (today,))
        records = cursor.fetchall()  # 列表，每个元素是字典

        # ✅ 关闭连接
        cursor.close()
        connection.close()

        # ✅ 返回 JSON 格式数据
        return jsonify({
            "status": "success",
            "data": records,
            "date": today.strftime("%Y-%m-%d"),
            "count": len(records)
        })

    except Exception as e:
        print("[API ERROR] 查询记录失败：", e)
        return jsonify({
            "status": "error",
            "message": f"查询记录时发生错误：{e}"
        }), 500



if __name__ == '__main__':
    app.run(debug=True)
    #app.run(host="0.0.0.0", port=80)